﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using System.Xml;

namespace AutomaticBackupSql
{
    public delegate void DelegateSqlBackup(object orderID);
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            Text = "数据库自动备份系统V10.0.0.6";
            label1.Text = string.Format("当前时间：【{0}】正在监测中", DateTime.Now.ToString("yyyy年MM月dd日 HH时mm分"));
            ComBoxImtesAdd();
        }

        #region 表格信息显示插入日志
        //插入信息到表格中
        public void AddFromsInfo(string listinfo, string liststate)
        {
            string datetime = DateTime.Now.ToString("yyyyMMdd HH:mm:ss");
            ListViewItem listViewItem = new ListViewItem
            {
                Text = datetime
            };
            listViewItem.SubItems.Add(listinfo);
            listViewItem.SubItems.Add(liststate);
            listView1.Items.Insert(0, listViewItem);
            //插入日志
            InsertLog(datetime, listinfo, liststate);
        }
        //插入日志
        private void InsertLog(string datetime, string listinfo, string liststate)
        {
            string txtPath = Application.StartupPath + "\\Log\\";
            if (!Directory.Exists(txtPath))
                Directory.CreateDirectory(txtPath);
            File.AppendAllText(String.Format("{0}.txt", txtPath + DateTime.Now.ToString("yyyyMMdd"))
                , String.Format("{0},{1},{2}\r\n", datetime, listinfo, liststate), Encoding.Default);
        }

        #endregion

        #region 执行进程后处理数据
        public delegate void AddInfoObject(string listinfo, string liststate, object results);
        //执行进程后处理数据
        public void UpdateInfo(string listinfo, string listsate, object results)
        {
            if (listView1.InvokeRequired)
            {
                AddInfoObject stcb = new AddInfoObject(UpdateInfo);
                Invoke(stcb, new object[] { listinfo, listsate, results });
            }
            else
            {
                //将传递信息打印
                if (results == null)
                {
                    AddFromsInfo(listinfo, listsate);
                }
            }
        }
        #endregion

        #region XML获取信息语句
        /// <summary>
        /// 获取节点数据组
        /// </summary>
        /// <param name="nodeName"></param>
        /// <returns></returns>
        public static string[] XmlInnerTextS(string nodeName)
        {
            try
            {
                List<string> vs = new List<string>();
                XmlDocument xml = new XmlDocument();//声明xml    
                xml.Load("AutoConfig.xml");//按路径读xml文件 
                XmlNodeList xn = xml.SelectNodes(nodeName);
                foreach (XmlNode item in xn)
                {
                    vs.Add(item.InnerText);
                }
                return vs.ToArray();
            }
            catch
            {
                return null;//如果错误返回空
            }
        }
        #endregion

        #region 插入数据到列表中
        public void ComBoxImtesAdd()
        {
            foreach (string item in SqlBakTable)
            {
                comboBox1.Items.Add(item);
            }
            comboBox1.Text = "全部";
        }
        #endregion

        #region 逐个备份数据库
        //自动备份数据列表
        public string[] SqlBakTable = XmlInnerTextS("XML/CONFIG/SQL/SQLNAME");
        public int threadNumber = 0;
        private void BackupSqlStart()
        {
            if (threadNumber < SqlBakTable.Length)
            {
                Thread t = new Thread(BackupSql);
                t.Start(SqlBakTable[threadNumber]);
                threadNumber++;
            }
            else
            {
                threadNumber = 0;
            }

        }
        //执行完成后执行下一项异步
        private void BackupSql(object sqlName)
        {
            DelegateSqlBackup delegateSqlBackup = new DelegateSqlBackup(BackupSqlNew);
            delegateSqlBackup(sqlName);
            BackupSqlStart();
        }
        #endregion

        #region 备份数据库语句
        //执行备份数据进程
        private void BackupSqlNew(object sqlName)
        {
            string sqlBackupName = string.Format("{0}_{1}.bak", sqlName.ToString(), DateTime.Now.ToString("yyyyMMdd_HHmmssfff"));
            //设置路劲
            string pathDirectory = string.Format("D:\\SqlBak\\{0}", sqlName);
            //路劲不存在则创建
            if (!Directory.Exists(pathDirectory))
                Directory.CreateDirectory(pathDirectory);
            //设置完整路劲到包含文件名
            string path = string.Format("D:\\SqlBak\\{0}\\{1}", sqlName, sqlBackupName);
            string sql = "backup database [{0}] to disk='{1}'";
            sql = string.Format(sql, sqlName.ToString(), path);
            object sqlPrint = SqlToPrint(sql, out bool sqlState);
            if (sqlState)
            {
                UpdateInfo(string.Format("数据库【{0}】备份完成", sqlName), "成功", null);
                return;
            }
            else
            {
                UpdateInfo(sqlPrint.ToString().Replace(",", "").Replace("\n", "").Replace("\r", ""), "消息", null);
                UpdateInfo(string.Format("数据库【{0}】备份失败", sqlName), "失败", null);
                return;
            }
        }
        #endregion

        #region 定时语句查询语句
        private void timer1_Tick(object sender, EventArgs e)
        {
            string dateNowTime = DateTime.Now.ToString("HHmm");
            string[] bakTime = { "1300", "2000", "0300" };
            //判定时间是否存在
            bool exists = ((System.Collections.IList)bakTime).Contains(dateNowTime);
            if (exists)
            {
                label1.Text = string.Format("当前时间：【{0}】正在执行备份", DateTime.Now.ToString("yyyy年MM月dd日 HH时mm分"));
                BackupSqlStart();
            }
            else
            {
                label1.Text = string.Format("当前时间：【{0}】正在监测中", DateTime.Now.ToString("yyyy年MM月dd日 HH时mm分"));
            }
        }
        #endregion

        #region 数据库查询语句
        public string contr = XmlInnerTextS("XML/CONFIG/SQLCONFIG")[0];
        //返回数据后输出的消息内容
        public string SqlToPrint(string sql, out bool sqlState)
        {
            sqlState = false;
            SqlConnection con = new SqlConnection(contr);
            SqlCommand com = new SqlCommand(sql, con)
            {
                CommandTimeout = 180
            };
            try
            {
                string message = "";
                void InfoMessage_SqlInfoMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
                {
                    message = e.Message;
                    foreach (SqlError item in e.Errors)
                    {
                        UpdateInfo(item.Message.Replace(",", ""), "消息", null);
                    }
                }
                con.InfoMessage += new SqlInfoMessageEventHandler(InfoMessage_SqlInfoMessageEventHandler);
                con.Open();
                com.ExecuteNonQuery();
                sqlState = true;
                return message;
            }
            catch (Exception ex)
            {
                sqlState = false;
                return ex.Message;
            }
            finally
            {
                con.Dispose();
                com.Dispose();
            }
        }
        #endregion

        #region 手动备份语句
        private void button1_Click(object sender, EventArgs e)
        {
            label1.Text = string.Format("当前时间：【{0}】正在执行备份", DateTime.Now.ToString("yyyy年MM月dd日 HH时mm分"));
            if (comboBox1.Text == "全部")
            {
                BackupSqlStart();
            }
            else
            {
                Thread t = new Thread(BackupSqlNew);
                t.Start(comboBox1.Text);
            }
        }
        #endregion
    }
}
